from fun_workdays import GetWorkdays, GetMonthRange
from datetime import date, timedelta

from sqlalchemy import func
from models import db, User, Task, Subcategory, ProjectGrade, DepartmentGrade


def GetOffworkSubcategories():
    offwork_category_id = 33

    return [
        s
        for (s,) in (
            db.session.query(Subcategory.name)
            .filter(Subcategory.categoryid == offwork_category_id)
            .filter(Subcategory.delete == False)
        )
    ]


def GetUsers():
    filter_user_ids = (1, 4, 7, 116, 117, 118)
    users = (
        db.session.query(User.id, User.no, User.name, User.position)
        .filter(User.delete == False)
        .filter(~User.id.in_(filter_user_ids))
        .all()
    )
    return users


def GetUserManhourList(year, month):
    workdays, weekends = GetWorkdays(year, month)
    month_start, month_end = GetMonthRange(year, month)

    users = GetUsers()
    filter_user_ids = (1, 4, 7, 116, 117, 118)
    offwork_category_id = 33
    tasks = (
        db.session.query(
            Task.userid, Task.tasktime, func.sum(Task.manhour).label("hours")
        )
        .filter(Task.delete == False)
        .filter(~Task.userid.in_(filter_user_ids))
        .filter(Task.categoryid != offwork_category_id)
        .filter(Task.tasktime >= month_start)
        .filter(Task.tasktime <= month_end)
        .join(User, User.id == Task.userid)
        .filter(User.delete == False)
        .group_by(Task.userid)
        .group_by(Task.tasktime)
        .all()
    )

    total = {user.id: {"worktime": 0, "overtime": 0} for user in users}

    for task in tasks:
        userid = task.userid
        manhour = task.hours
        day = task.tasktime.date()

        if day in weekends:
            total[userid]["overtime"] = total[userid]["overtime"] + manhour
        else:
            if manhour > 8:
                total[userid]["overtime"] = total[userid]["overtime"] + (manhour - 8)
                total[userid]["worktime"] = total[userid]["worktime"] + 8
            else:
                total[userid]["worktime"] = total[userid]["worktime"] + manhour

    return total


# get user offwork detail with sub category
def GetUserOffworkDetail(year, month, user_id):
    workdays, weekends = GetWorkdays(year, month)
    month_start, month_end = GetMonthRange(year, month)

    offwork_category_id = 33

    offwork_subcategories = GetOffworkSubcategories()

    tasks = (
        db.session.query(
            Subcategory.name.label("subcategory"),
            Task.tasktime,
            func.sum(Task.manhour).label("hours"),
        )
        .filter(Task.delete == False)
        .filter(Task.userid == user_id)
        .filter(Task.categoryid == offwork_category_id)
        .filter(Task.tasktime >= month_start)
        .filter(Task.tasktime <= month_end)
        .join(Task, Subcategory.id == Task.subcategoryid)
        .group_by(Subcategory.name)
        .group_by(Task.tasktime)
        .all()
    )

    total = {field: 0 for field in offwork_subcategories}

    for task in tasks:
        manhour = task.hours
        subcategory = task.subcategory
        day = task.tasktime.date()
        if day in workdays:
            total[subcategory] = total[subcategory] + manhour

    return filter(lambda v: v[1] > 0, total.items())


# get user offwork with sub category
def GetUserOffworkList(year, month):
    workdays, weekends = GetWorkdays(year, month)
    month_start, month_end = GetMonthRange(year, month)

    filter_user_ids = (1, 4, 7, 116, 117, 118)
    offwork_category_id = 33

    offwork_subcategories = GetOffworkSubcategories()

    tasks = (
        db.session.query(
            Task.userid,
            Task.tasktime,
            Subcategory.name.label("subcategory"),
            func.sum(Task.manhour).label("hours"),
        )
        .filter(Task.delete == False)
        .filter(~Task.userid.in_(filter_user_ids))
        .filter(Task.categoryid == offwork_category_id)
        .filter(Task.tasktime >= month_start)
        .filter(Task.tasktime <= month_end)
        .join(User, User.id == Task.userid)
        .filter(User.delete == False)
        .join(Subcategory, Subcategory.id == Task.subcategoryid)
        .group_by(Task.userid)
        .group_by(Task.tasktime)
        .group_by(Subcategory.name)
        .all()
    )

    users = GetUsers()

    total = {user.id: {field: 0 for field in offwork_subcategories} for user in users}

    for task in tasks:
        userid = task.userid
        manhour = task.hours
        subcategory = task.subcategory
        day = task.tasktime.date()

        # print(userid, manhour, subcategory, day)
        if day in workdays:
            total[userid][subcategory] = total[userid][subcategory] + manhour

    return total


# get user offwork without sub category
def GetUserOffworkListSummary(year, month):
    workdays, weekends = GetWorkdays(year, month)
    month_start, month_end = GetMonthRange(year, month)

    filter_user_ids = (1, 4, 7, 116, 117, 118)
    offwork_category_id = 33

    tasks = (
        db.session.query(
            Task.userid, Task.tasktime, func.sum(Task.manhour).label("hours"),
        )
        .filter(Task.delete == False)
        .filter(~Task.userid.in_(filter_user_ids))
        .filter(Task.categoryid == offwork_category_id)
        .filter(Task.tasktime >= month_start)
        .filter(Task.tasktime <= month_end)
        .join(User, User.id == Task.userid)
        .filter(User.delete == False)
        .group_by(Task.userid)
        .group_by(Task.tasktime)
        .all()
    )

    users = GetUsers()

    total = {user.id: 0 for user in users}

    for task in tasks:
        userid = task.userid
        manhour = task.hours
        day = task.tasktime.date()

        if day in workdays:
            total[userid] = total[userid] + manhour

    return total


# get monthly detail with sub category
def GetMonthlyList(year, month):
    manhours = GetUserManhourList(year, month)
    offworks = GetUserOffworkList(year, month)
    grades = GetAssess(year, month)
    users = GetUsers()

    fields_work = ["worktime", "overtime"]
    fields_grade = ["departgrade", "projectgrade"]
    fields_offwork = GetOffworkSubcategories()
    fields = ["id", "工号", "姓名", "部门"] + ["部门考核", "项目考核"] + ["出勤", "加班"] + fields_offwork

    data = []
    for user in users:
        row = []
        row.append(user.id)
        row.append(user.no)
        row.append(user.name)
        row.append(user.position)
        for f in fields_grade:
            row.append(grades[user.id][f])
        for f in fields_work:
            row.append(manhours[user.id][f])
        for f in fields_offwork:
            row.append(offworks[user.id][f])
        data.append(row)
    return (fields, data)


# get monthly detail without sub category
def GetMonthlyListSummary(year, month):
    manhours = GetUserManhourList(year, month)
    offworks = GetUserOffworkListSummary(year, month)
    grades = GetAssess(year, month)
    users = GetUsers()

    fields_work = ["worktime", "overtime"]
    fields_grade = ["departgrade", "projectgrade"]
    fields_offwork = GetOffworkSubcategories()
    fields = ["id", "工号", "姓名", "部门"] + ["部门考核", "项目考核"] + ["出勤", "加班"] + ["休假"]

    data = []
    for user in users:
        row = []
        row.append(user.id)
        row.append(user.no)
        row.append(user.name)
        row.append(user.position)
        for f in fields_grade:
            row.append(grades[user.id][f])
        for f in fields_work:
            row.append(manhours[user.id][f])
        # for f in fields_offwork:
        row.append(offworks[user.id])
        data.append(row)
    return (fields, data)


def GetAssess(year, month):

    project_average_grade = (
        db.session.query(
            ProjectGrade.user_id, func.avg(ProjectGrade.grade).label("avg"),
        )
        .filter(ProjectGrade.delete == False)
        .filter(ProjectGrade.year == year)
        .filter(ProjectGrade.month == month)
        .group_by(ProjectGrade.user_id)
        .subquery()
    )

    users = (
        db.session.query(
            User.id,
            DepartmentGrade.grade.label("departgrade"),
            project_average_grade.c.avg.label("projectgrade"),
        )
        .filter(~User.id.in_((1, 4, 7, 116, 117, 118)))
        .filter(User.delete == False)
        .join(
            DepartmentGrade,
            (DepartmentGrade.year == year)
            & (DepartmentGrade.month == month)
            & (DepartmentGrade.delete == False)
            & (DepartmentGrade.user_id == User.id),
            isouter=True,
        )
        .join(
            project_average_grade,
            project_average_grade.c.user_id == User.id,
            isouter=True,
        )
        .all()
    )

    total = {
        user.id: {"departgrade": user.departgrade, "projectgrade": user.projectgrade}
        for user in users
    }
    return total


if __name__ == "__main__":
    year = 2019
    month = 11

    fields, data = GetMonthlyList(year, month)
    print(fields)
    print(data[:3])

    # for i in range(1, 13):
    #     print(GetUserManhourList(2019, i)[2])
    #     print(GetUserOffworkList(2019, i)[2])

